package net.wanho.index.dao.impl;

import net.wanho.commons.exception.DaoException;
import net.wanho.commons.exception.DataAccessException;
import net.wanho.commons.factory.BeanFactory;
import net.wanho.commons.util.JDBCTemplate;
import net.wanho.customerManage.vo.BusinessAndCustomerVo;
import net.wanho.index.dao.IndexDao;
import net.wanho.personalOffice.po.IndividualTask;
import net.wanho.systemSettings.vo.EmployeeVo;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class IndexDaoImpl implements IndexDao {
    private JDBCTemplate jdbcTemplate = BeanFactory.getBean(JDBCTemplate.class) ;
    @Override
    public List<BusinessAndCustomerVo> selectCurrMonthBusinessAndCustomerVo() throws DaoException {
        final String SELECT_BUSINESS_AND_CUSTOMER_CURR_MONTH_SQL = "(SELECT\n" +
                "  '客户'           TYPE,\n" +
                "  c.CUSTOMER_ID      id,\n" +
                "  c.CUSTOMER_NAME    NAME,\n" +
                "  l.NAME             linkManName,\n" +
                "  bs.SOURCE_NAME     SOURCENAME,\n" +
                "  c.UPDATE_TIME      UPDATETIME\n" +
                "FROM customer c\n" +
                "  LEFT JOIN link_man l\n" +
                "    ON c.PRINPICAL = l.LINK_MAIN_ID\n" +
                "  LEFT JOIN business_source bs\n" +
                "    ON c.SOURCE_ID = bs.SOURCE_ID\n" +
                "WHERE DATE_FORMAT(c.create_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m'))\n" +
                "UNION ALL\n" +
                "(SELECT\n" +
                "  '商机'           TYPE,\n" +
                "  b.BUSINESS_ID      id,\n" +
                "  b.BUSINESS_NAME    NAME,\n" +
                "  l.NAME             linkManName,\n" +
                "  bs.SOURCE_NAME     SOURCENAME,\n" +
                "  b.UPDATE_TIME        UPDATETIME\n" +
                "FROM business b\n" +
                "  LEFT JOIN link_man l\n" +
                "    ON b.LINK_MAIN_ID = l.LINK_MAIN_ID\n" +
                "  LEFT JOIN business_source bs\n" +
                "    ON b.BUSINESS_SOURCE_ID = bs.SOURCE_ID\n" +
                "WHERE DATE_FORMAT(b.UPDATE_TIME,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m'))" ;
        try {
            return jdbcTemplate.queryForList(SELECT_BUSINESS_AND_CUSTOMER_CURR_MONTH_SQL, BusinessAndCustomerVo.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DaoException(e.getMessage()) ;
        }
    }

    @Override
    public Map<String, Integer> countAll() throws DaoException {
        final String SELECT_COUNT_BUSINESS_CUSTOMER_ORDER_SQL = "SELECT\n" +
                "  SUM(day7NewCustomerCount) day7NewCustomerCount ,\n" +
                "  SUM(day7NewBusiness) day7NewBusiness ,\n" +
                "  SUM(day7NewOrder) day7NewOrder ,\n" +
                "  SUM(month1NewCustomer) month1NewCustomer \n" +
                "FROM\n" +
                "(\n" +
                " SELECT \n" +
                "  CASE WHEN TYPE = 'day7NewCustomerCount' THEN num END  day7NewCustomerCount ,\n" +
                "  CASE WHEN TYPE = 'day7NewBusiness' THEN num END  day7NewBusiness ,\n" +
                "  CASE WHEN TYPE = 'day7NewOrder' THEN num END  day7NewOrder ,\n" +
                "  CASE WHEN TYPE = 'month1NewCustomer' THEN num END  month1NewCustomer \n" +
                "FROM\n" +
                " ( (SELECT COUNT(0) num ,'day7NewCustomerCount' TYPE FROM customer WHERE create_time > DATE_SUB(NOW(), INTERVAL 7 DAY))\n" +
                "UNION ALL \n" +
                "(SELECT COUNT(0) num ,'day7NewBusiness' TYPE FROM business WHERE UPDATE_TIME > DATE_SUB(NOW(), INTERVAL 7 DAY))\n" +
                "UNION ALL \n" +
                "(SELECT COUNT(0) num ,'day7NewOrder' TYPE  FROM orders WHERE create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)) \n" +
                "UNION ALL \n" +
                "(SELECT COUNT(0) num ,'month1NewCustomer' TYPE FROM customer WHERE create_time > DATE_SUB(NOW(), INTERVAL 30 DAY))\n" +
                ") t\n" +
                ") tt" ;
        try {
            return jdbcTemplate.queryForMap(SELECT_COUNT_BUSINESS_CUSTOMER_ORDER_SQL);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DaoException(e.getMessage()) ;
        }
    }
}
